package com.nokia.ion.sdu.cdr_decoder;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.LinkedBlockingQueue;

/**
 * Created by vicwang on 2017/9/6.
 */
public class Sqlite3Writer extends CdrWriter {

    private int idx = 0;

    private Statement dbStatement;
    private Connection conn;

    private Configuration.Sqlite3Configure sqlite3Configure;

    private static final String CREATE_TABLE_SQL = "CREATE TABLE cdr" +
            " (node_id           TEXT, " +
//                " apn             TEXT, " +
//                " p_addr             TEXT, " +
            " s_addr             TEXT, " +
//                " msisdn            INT, " +
//                " imei            INT, " +
            " imsi            INT, " +
            " rat_type             INT, " +
            " uli             TEXT, " +
            " open_time            INT, " +
            " duration          INT, " +
            " cause             INT, " +
            " charging_id          INT)";

    public Sqlite3Writer(LinkedBlockingQueue<Cdr> queue, Configuration configuration)
            throws IOException, ClassNotFoundException, SQLException {
        super(queue, configuration);

        this.sqlite3Configure = configuration.sqlite3;

        File file = new File(String.format("%s/%s", sqlite3Configure.outputPath, sqlite3Configure.dbName));
        if (file.exists()) {
            file.delete();
        }

        String url = String.format("jdbc:sqlite:%s/%s", sqlite3Configure.outputPath, sqlite3Configure.dbName);
        Class.forName("org.sqlite.JDBC");

        // create a connection to the database
        conn = DriverManager.getConnection(url);

        dbStatement = conn.createStatement();
        dbStatement.executeUpdate("drop table if exists cdr");
        dbStatement.executeUpdate(CREATE_TABLE_SQL);
//        dbStatement.executeUpdate("create index if not exists cdr_index on cdr(node_id,imsi,charging_id)");

        conn.setAutoCommit(false);

        System.out.println("The SQLite Connection has been established.");
    }

    private String generateInsertSql(Cdr cdr) {
        return String.format("insert into cdr(node_id, s_addr, imsi, rat_type, uli, open_time, duration, cause, charging_id) values ('%s', '%s', %d, %d, '%s', '%d', %d, %d, %d)",
                cdr.nodeID, cdr.servingNodeAddress, cdr.servedIMSI,
                cdr.rATType, cdr.userLocationInformation, cdr.recordOpeningTime.getTime() / 1000, cdr.duration,
                cdr.causeForRecClosing, cdr.chargingId);
    }

    @Override
    public void write(Cdr cdr) {
        try {
            dbStatement.execute(generateInsertSql(cdr));

            idx++;
            if (idx >= sqlite3Configure.commitBufSize) {
                conn.commit();
                idx = 0;
            }
        } catch (SQLException e) {
            System.err.println(e);
        }
    }

    @Override
    void close() {
        try {
            conn.commit();
            conn.close();
        } catch (SQLException e) {
            System.err.println(e);
        }
    }
}
